package com.wondersgroup.fjzlpt.org.dao.impl.tdgl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.quick.framework.boot.repository.sql.dialect.DialectProxy;
import com.wondersgroup.fjzlpt.org.util.ControllerCommonUtil;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.quick.framework.boot.repository.sql.bean.SQLBean;
import com.quick.framework.boot.repository.sql.bean.page.PageQuerySQLBean;
import com.quick.framework.boot.repository.sql.bean.table.TableBean;
import com.quick.framework.boot.repository.sql.jdbcTemplate.impl.BaseJTDAOImpl;
import com.quick.framework.boot.repository.sql.jdbcTemplate.rs.ResultHandlerConstant;
import com.wondersgroup.fjzlpt.org.dao.inter.tdgl.TdjbxxDao;

@Repository
public class TdjbxxDaoImpl extends BaseJTDAOImpl implements TdjbxxDao {

	@Autowired
	private DialectProxy dialectProxy;

	@Value("${common.WSGLJGLBDM}")
	private String GLJGLBDM;

	@Override
	@Autowired
	@Qualifier("TB_TDGL_TDJBXX")
	public void setTb(TableBean tb) {
		super.setTb(tb);
	}

	
	@Override
	public JSONObject queryYljgryxxWithConditionByPage(Map<String, String> map) {
		StringBuffer sql = new StringBuffer("select a.YLJGMC,b.USERID,b.SFZH,b.XM,b.XB,b.SJHM,c.KSMC,\n");
		sql.append("(select XMMC from TB_ZY_XB where XMDM=b.XB) XBMC \n");
		sql.append("from TB_ZD_YLJGXX a left join TB_ZD_YLJGRYXX b on a.YLJGDM=b.YLJGDM \n");
		sql.append("left join TB_ZD_KSXX c on b.KSDM=c.KSDM and b.PTJGID=c.PTJGID where b.USERID is not null \n ");

		List<String> sqlParams = new ArrayList<String>();
		//医疗机构名称
		String value = map.get("YLJGMC");
		if(!StringUtils.isEmpty(value)){
			sql.append("and a.YLJGMC like ?");
			sqlParams.add("%"+value+"%");
		}
		//平台机构代码
		value = map.get("PTJGID");
		if(!StringUtils.isEmpty(value)){
			sql.append("and a.PTJGID = ?");
			sqlParams.add(value);
		}
		//医生姓名
		value = map.get("XM");
		if(!StringUtils.isEmpty(value)){
			sql.append("and b.XM like ?");
			sqlParams.add("%"+value+"%");
		}
		
		int pageSize = Integer.parseInt(map.get("rows"));
		int pageNumber = Integer.parseInt(map.get("page"));
		PageQuerySQLBean pageBean = new PageQuerySQLBean(sql.toString(), pageSize, pageNumber, sqlParams.toArray());
		
		return super.queryPage(pageBean,ResultHandlerConstant.JSONARRAY);
	}
	
	/**
	 * 根据名称模糊查询机构信息-分页查询
	 * @param map
	 * @return
	 */
	@Override
	public JSONObject queryYljgxxByPage(Map<String, String> map){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select a.PTJGID,a.YLJGMC,");
		sqlBuf.append("(select XZQHMC from TB_ZY_XZQH where XZQHDM=a.XZQHDM) XZQHMC,\n");
		sqlBuf.append("(select XMMC from TB_ZY_JGLB where XMDM=a.WSJGLBDM) WSJGLBMC,\n");
		sqlBuf.append("(select XMMC from TB_ZY_YYDJ where XMDM=a.YYDJDM) YYDJMC \n");
		sqlBuf.append("from TB_ZD_YLJGXX a where a.YLJGMC like ? ");
		
		int pageSize = Integer.parseInt(map.get("rows"));
		int pageNumber = Integer.parseInt(map.get("page"));
		String YLJGMC = '%' + map.get("YLJGMC") + '%';
		PageQuerySQLBean pageBean = new PageQuerySQLBean(sqlBuf.toString(), pageSize, pageNumber, "", YLJGMC);
		return super.queryPage(pageBean, ResultHandlerConstant.JSONARRAY);
	}
	
	/**
	 * 查询团队长所在的平台的ptjgid
	 * @param dtzUserid
	 * @return
	 */
	@Override
	public String getDztPtjgid(String dtzUserid){
		SQLBean bean = new SQLBean("select PTJGID from TB_ZD_YLJGRYXX where USERID = ?",dtzUserid);
		JSONObject userObj = super.queryOne(bean);
		return userObj.getString("PTJGID");
	}
	
	/**
	 * 根据条件查询所有团队(导出)
	 * @return
	 */
	@Override
	public JSONArray queryAll(Map<String, String> map) {
		
		JSONObject sqlObj = this.getPageSql(map);
		List<String> sqlParams = (List)sqlObj.get("sqlParams");
		SQLBean bean = new SQLBean(sqlObj.getString("sql"), sqlParams.toArray());
		return super.queryList(bean);
		
	}

	/**
	 * 根据条件分页查询团队列表
	 * @param map
	 * @return
	 */
	@Override
	public JSONObject queryPage(Map<String, String> map) {
		JSONObject sqlObj = this.getPageSql(map);
		List<String> sqlParams = (List)sqlObj.get("sqlParams");
		
		int pageSize = Integer.parseInt(map.get("rows"));
		int pageNumber = Integer.parseInt(map.get("page"));
		PageQuerySQLBean pageBean = new PageQuerySQLBean(sqlObj.getString("sql"), pageSize, pageNumber , null,sqlParams.toArray());
		
		return super.queryPage(pageBean, ResultHandlerConstant.JSONARRAY);
	}
	
	/**
	 * 生成分页语句
	 * @param map
	 * @return
	 */
	private JSONObject getPageSql(Map<String, String> map){
		String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());
		JSONObject result = new JSONObject();
		
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select * from V_JTQY_TDXX a where 1=1 \n");
		List<String> sqlParams = new ArrayList<>();
		//团队名称
		String value = map.get("TDMC");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append(" and a.TDMC like ?");
			sqlParams.add("%"+value+"%");
		}
		//审核状态
		value = map.get("SHZT");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append(" and a.SHZT = ? ");
			sqlParams.add(value);
		}
		//医疗机构名称
		value = map.get("YLJGMC");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append(" and a.YLJGMC like ? ");
			sqlParams.add("%"+value+"%");
		}

		//管理机构
		value = map.get("WSJGLBDM");
		if(!StringUtils.isEmpty(value) && value.indexOf(GLJGLBDM) != -1){
			String XZQHDM = map.get("XZQHDM");
			sqlBuf.append(" and a.XZQHDM like ? and a.SHZT <> 0 ");
			sqlParams.add(XZQHDM+"%");
			//医疗机构ID
			value = map.get("YLJGID");
			if(!StringUtils.isEmpty(value)){
				sqlBuf.append("and a.YLJGID = ? ");
				sqlParams.add(value);
			}
		}
		//当前登录用户--平台管理员
		else if(!StringUtils.isEmpty(value = map.get("isPtgly"))){
			sqlBuf.append(" and a.SHZT <> 0 ");
		}
		//当前登录用户--医院管理员
		else if(!StringUtils.isEmpty(value = map.get("isYygly"))){
			sqlBuf.append(" and a.YLJGID = ? and (a.SHZT <> 0 or a.XJR = ?) ");
			sqlParams.add(map.get("YLJGID"));
			sqlParams.add(map.get("USERID"));
		}
		//当前登录用户--团队长或普通成员
		else if(!StringUtils.isEmpty(value = map.get("isTdzOrCy"))){
			sqlBuf.append(" and (a.XJR = ? or (a.SHZT<>0 and a.TDCYID like ?)) ");
			value = map.get("USERID");
			sqlParams.add(value);
			sqlParams.add("%" + value + "%"); //只要团队和当前医生有关，全部查询
		}
		
		sqlBuf.append("order by a.YLJGID,a.CJSJ desc");
		result.put("sql", sqlBuf.toString());
		result.put("sqlParams", sqlParams);
		return result;
	}


	/**
	 * 根据用户信息查询他所在的所有团队
	 * @param map
	 * @return
	 */
	@Override
	public JSONArray queryTdxxByUserInfo(Map<String, String> map) {
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select a.*,b.TDJS from TB_TDGL_TDJBXX a left join TB_TDGL_TDRY b on a.TDID=b.TDID where 1 = 1 ");
		List<String> sqlParams = new ArrayList<>();
		//团队状态（0不可用/1可用）
		String value = map.get("TDZT");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.TDZT = ? ");
			sqlParams.add(value);
		}
		//医疗机构id
		value = map.get("YLJGID");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.YLJGID = ? ");
			sqlParams.add(value);
		}
		//团队名称
		value = map.get("TDID");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.TDID = ? ");
			sqlParams.add(value);
		}
		//团队名称
		value = map.get("TDMC");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.TDMC like ? ");
			sqlParams.add("%"+value+"%");
		}
		//用户id
		value = map.get("USERID");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and b.USERID = ? ");
			sqlParams.add(value);
		}
		//团队角色
		value = map.get("TDJS");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and b.TDJS = ? ");
			sqlParams.add(value);
		}
		SQLBean bean = new SQLBean(sqlBuf.toString(), sqlParams.toArray());
		return super.queryList(bean);
	}

	/**
	 * 根据机构信息查询他所在的所有团队
	 * @param map
	 * @return
	 */
	@Override
	public JSONArray queryTdxxByInfo(Map<String, String> map){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("SELECT A.* from TB_TDGL_TDJBXX A LEFT JOIN TB_ZD_YLJGXX B ON A.YLJGID = B.PTJGID  where 1 = 1 ");
		List<String> sqlParams = new ArrayList<>();
		//团队状态（0不可用/1可用）
		String value = map.get("TDZT");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and A.TDZT = ? ");
			sqlParams.add(value);
		}
		//医疗机构id
		value = map.get("PTJGID");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and A.YLJGID = ? ");
			sqlParams.add(value);
		}
		//团队名称
		value = map.get("TDID");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and A.TDID = ? ");
			sqlParams.add(value);
		}
		//团队名称
		value = map.get("TDMC");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and A.TDMC like ? ");
			sqlParams.add("%"+value+"%");
		}
		//行政区划
		value = map.get("XZQHDM");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and B.XZQHDM like ? ");
			sqlParams.add(value+"%");
		}
		SQLBean bean = new SQLBean(sqlBuf.toString(), sqlParams.toArray());
		return super.queryList(bean);
	}

	
	@Override
	public JSONArray findAll(Map<String, String> map) {
		JSONArray jsonArray = null;
		if(map!=null && map.size()>0){
			StringBuffer sql = new StringBuffer("select b.TDID,b.TDMC from TB_TDGL_TDJBXX b where 1=1 \n");
			List list = new ArrayList();
			if(!StringUtils.isEmpty(map.get("TDZT")))
			{
				sql.append("and b.TDZT=?");
				list.add(map.get("TDZT"));
			}
			if(!StringUtils.isEmpty(map.get("YLJGID")))
			{
				sql.append("and b.YLJGID=?");
				list.add(map.get("YLJGID"));
			}
			SQLBean sqlBean = new SQLBean(sql.toString(),list.toArray());
			jsonArray= super.queryList(sqlBean);
		}
		return jsonArray;
	}

	/**
	 * 判断是否存在相同的团队名称
	 * @return
	 */
	@Override
	public JSONObject checkSaveName(Map<String, String> map) {
		//不包括SHZT=4的数据----SHZT=4 : 已解散团队
		StringBuffer sqlBuf = new StringBuffer("select TDID from TB_TDGL_TDJBXX where SHZT<>'4' and TDMC= ? ");
		sqlBuf.append("and YLJGID = ?"); //相同机构团队名称不能重复，不同机构可以重复
		List<String> sqlParams = new ArrayList<String>();
		sqlParams.add(map.get("TDMC"));
		sqlParams.add(map.get("YLJGID"));
		
		String TDID = map.get("TDID");
		if(!StringUtils.isEmpty(TDID)){
			sqlBuf.append("and TDID <> ?");
			sqlParams.add(TDID);
		}
		SQLBean bean = new SQLBean(sqlBuf.toString(), sqlParams.toArray());
		
		return super.queryOne(bean);
	}


	/**
	 * 分页查询团队信息到健康城市系统
	 * @param map {XZQHDM=xxx,YLJGDM=xxx,pageSize=xxx,pageNumber=xxx}
	 * @return
	 */
	public JSONObject queryTdxxByPageToJkcs(Map<String, String> map){
		ControllerCommonUtil.pageFormat(map);
		StringBuffer sqlBuf = new StringBuffer();
		List<String> sqlParams = new ArrayList<String>();
		sqlBuf.append("select a.TDID,a.TDMC,a.TDJJ,a.CJSJ,b.PTJGID YLJGID,b.XZQHDM,b.YLJGMC,case when c.LJQYRS is null then 0 else c.LJQYRS end LJQYRS,\n");
		// 机构类别
		sqlBuf.append("(SELECT XMMC from TB_ZY_JGLB where XMDM=b.WSJGLBDM) JGLB \n");
		sqlBuf.append("from TB_TDGL_TDJBXX a left join TB_ZD_YLJGXX b on a.YLJGID=b.PTJGID \n");
		// 累计签约人数
		sqlBuf.append("left join (select ylTDID,count(distinct JMXXID) LJQYRS from TB_JTQY_QYXX where QYLX<>'00' and QYLX<>'06' group by YLTDID) c on a.TDID=c.YLTDID \n");
		sqlBuf.append("where a.tdzt = '1' ");
		
		// 行政区划代码
		String value = map.get("XZQHDM");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and b.XZQHDM like ? ");
			sqlParams.add(value + "%");
		}
		// 医疗机构代码
		value = map.get("YLJGDM");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.YLJGID = ? ");
			sqlParams.add(value);
		}
		PageQuerySQLBean pageBean = super.checkAndCreatePageQuerySQLBean(map, sqlBuf.toString(), "order by a.TDID", sqlParams.toArray());
		
		return super.queryPage(pageBean,ResultHandlerConstant.JSONARRAY);
	}

	/**
	 *	根据团队id查询该团队的联系电话
	 * @param TDID
	 * @return
	 */
	public String getLxdhByTdid(String TDID){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select LXDH from ").append(this.tb.getTableName()).append(" where TDID = ? ");
		SQLBean bean = new SQLBean(sqlBuf.toString(), TDID);
		JSONObject lxdhObj = super.queryOne(bean);

		if(!lxdhObj.isEmpty())
			return lxdhObj.getString("LXDH");
		else
			return null;
	}
	
}
